package service;
/*
@ClassName:     UserServiceImp
@Author:        YangXu
@Need:          
@Date:          2022/4/27
@Time:          2022-04-27 9:22
*/

//


import entity.User;
import utils.DB;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

public class UserServiceImp implements UserService{
    @Override
    public User findUser(String username, String password) {
        User user = null;
        DB db = new DB();
        Connection conn = null;
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            conn = db.getConnection();
            stmt = conn.createStatement();
            String sql = "select * from sysuser where account=? and password=?";

            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1,username);
            preparedStatement.setString(2,password);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                user=new User(resultSet.getString("account"),resultSet.getString("password"));
                return user;
            }
            return user;
        }catch (SQLException e) {
e.printStackTrace();
        }finally {
            try {
                db.close(conn,stmt,resultSet);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return user;
    }

    public List<User> FindList(String name){
        User user = null;
        DB db = new DB();
        Connection conn = null;
        Statement stmt = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<User> users = new ArrayList<>();
        try {
             conn = db.getConnection();
            if (name==null||name.equals("")){
                String sql = "SELECT * from sysuser s INNER JOIN dept d on s.deptId = d.deptId;";
                preparedStatement = conn.prepareStatement(sql);
                resultSet = preparedStatement.executeQuery();
                while (resultSet.next()) {
                    user=new User();
                    //系统账号
                    String account = resultSet.getString("account");
                    //用户名
                    String name1 = resultSet.getString("name");
                    //工作岗位
                    String job = resultSet.getString("job");
                    //部门名称
                    String deptName = resultSet.getString("deptName");

                    //设置学生类的成员遍历
                    user.setUsername(account);
                    user.setName(name1);
                    user.setJob(job);
                    user.setDeptName(deptName);

                    users.add(user);

                }
            }else {
                String sql = "SELECT * from sysuser left join dept on account like ? or name like ?";
                preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setString(1,'%'+name+'%');
                preparedStatement.setString(2,'%'+name+'%');
                 resultSet = preparedStatement.executeQuery();
                while (resultSet.next()) {
                    //系统账号
                    String account = resultSet.getString("account");
                    //用户名
                    String name1 = resultSet.getString("name");
                    //工作岗位
                    String job = resultSet.getString("job");
                    //部门名称
                    String deptName = resultSet.getString("deptName");

                    user=new User();
                    //设置学生类的成员遍历
                    user.setUsername(account);
                    user.setName(name1);
                    user.setJob(job);
                    user.setDeptName(deptName);
                    //添加到集合
                    users.add(user);
                }
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                db.close(conn,preparedStatement,resultSet);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return users;
    }
    public List<User> add(String username,String password,String name,String job,String deptId){
        ArrayList<User> Userlist = new ArrayList<>();
        User user = null;
        PreparedStatement preparedStatement = null;
        Connection connection=null;
        ResultSet resultSet = null;
        DB db=null;
        try{
            db = new DB();
            connection = db.getConnection();
            if (username.equals("")){
                return Userlist;
            }else {
            if (checkAccount(username)){
                return Userlist;
            }else {

                String sql = "insert into  sysuser (account,password,name,job,deptId) values (?,?,?,?,?)";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1, username);
                preparedStatement.setString(2, password);
                preparedStatement.setString(3, name);
                preparedStatement.setString(4, job);
                preparedStatement.setString(5, deptId);
                preparedStatement.executeUpdate();
                user = new User();
                user.setUsername(username);
                user.setPassword(password);
                user.setName(name);
                user.setJob(job);
                user.setDeptId(deptId);

                Userlist.add(user);
                return Userlist;
            }}
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return Userlist;
    }
    public boolean checkAccount(String account){
        DB db=null;
        Connection connection=null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet=null;
        try {
            db=new DB();
            connection=db.getConnection();
            String sql = "select account from sysuser where account=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,account);
            resultSet   = preparedStatement.executeQuery();
            while(resultSet.next()){
                String account1 = resultSet.getString("account");
                if (account1.equals(account)){
                    return true;
                }else {
                    return false;
                }
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                db.close(connection,preparedStatement,resultSet);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }
    public List<User> getDeptId(){
        User user = null;
        DB db = new DB();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet=null;
        List list = new ArrayList();
        try {
            connection = db.getConnection();
            PreparedStatement preparedStatement1 = connection.prepareStatement("select deptId,deptName  from dept");
            resultSet = preparedStatement1.executeQuery();
            while(resultSet.next()){
                user=new User();
                String deptId1 = resultSet.getString("deptId");
                String deptName = resultSet.getString("deptName");
                user.setDeptId(deptId1);
                user.setDeptName(deptName);

                list.add(user);
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                db.close(connection,preparedStatement,resultSet);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return list;
    }
}
/*

 */